package esi.t2no1.dao.contract;

import static esi.t2no1.util.DaoUtil.extractFirst;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;

import esi.t2no1.ws.model.ContractOutput;

/**
 * @author Kaarel Jõgeva
 */
public class ContractDAOImpl implements ContractDAO {

	private SimpleJdbcTemplate simpleJdbcTemplate;
	private SimpleJdbcInsert createContract;

	private static RowMapper<ContractDTO> contractMapper = new RowMapper<ContractDTO>() {

		@Override
		public ContractDTO mapRow(ResultSet rs, int rowNum) throws SQLException {
			ContractDTO c = new ContractDTO();
			c.setId(rs.getLong("id"));
			c.setNewSupplierId(rs.getString("newSupplierId"));
			c.setOldSupplierId(rs.getString("oldSupplierId"));
			c.setBalanceProviderId(rs.getString("balanceProviderId"));
			c.setNetworkOwnerId(rs.getString("networkOwnerId"));
			c.setClientContractNumber(rs.getString("clientContractNumber"));
			c.setFacilityId(rs.getString("facilityId"));
			c.setAreaId(rs.getString("areaId"));
			c.setSettlement(rs.getString("settlement"));
			c.setStartDate(rs.getDate("startDate"));
			c.setEndDate(rs.getDate("endDate"));
			c.setUserName(rs.getString("userName"));
			c.setUserBirthDate(rs.getDate("userBirthDate"));
			c.setUserAddressHouse(rs.getString("userAddressHouse"));
			c.setUserAddressStreet(rs.getString("userAddressStreet"));
			c.setUserAddressCity(rs.getString("userAddressCity"));
			c.setUserAddressCountry(rs.getString("userAddressCountry"));
			return c;
		}
	};

	@Override
	public Long createContract(ContractDTO contract) {
		return createContract.executeAndReturnKey(
				new BeanPropertySqlParameterSource(contract)).longValue();
	}

	@Override
	public ContractDTO getContract(Long id) {
		String sql = "select * from contract where id = ? limit 1";
		return extractFirst(simpleJdbcTemplate.query(sql, contractMapper, id));
	}

	@Override
	public ContractOutput getContractOutput(String clientContractNumber) {
		String sql = "select * from contract where clientContractNumber = ? limit 1";
		ContractDTO dto = extractFirst(simpleJdbcTemplate.query(sql,
				contractMapper, clientContractNumber));
		return dto == null ? null : dto.toContractOutput();
	}

	@Override
	public List<ContractDTO> getUserContracts(String userName,
			Date userBirthDate) {
		String sql = "select * from contract where userName = ? and userBirthDate = ? order by startDate";
		return simpleJdbcTemplate.query(sql, contractMapper, userName,
				userBirthDate);
	}

	@Override
	public Integer getContractByDate(String areaId, String facilityId,
			Date contractStartDate) {
		String sql = "select count(*) from contract where areaId = ? and facilityId = ? and startDate = ?";
		Integer dto = simpleJdbcTemplate.queryForInt(sql, areaId, facilityId,
				contractStartDate);
		return dto;
	}

	@Override
	public List<ContractDTO> getContracts() {
		String sql = "select * from contract order by id asc";
		return simpleJdbcTemplate.query(sql, contractMapper);
	}

	@Override
	public List<String> getFacilityIds() {
		String sql = "select distinct(facilityId) from contract";
		return simpleJdbcTemplate.query(sql, new RowMapper<String>() {

			@Override
			public String mapRow(ResultSet rs, int rowNum) throws SQLException {
				return rs.getString("facilityId");
			}

		});
	}

	@Override
	public String getAreaId(String facilityId) {
		String sql = "select areaId from contract where facilityId = ? order by id desc";
		return extractFirst(simpleJdbcTemplate.query(sql,
				new RowMapper<String>() {

					@Override
					public String mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						return rs.getString("areaId");
					}

				}, facilityId));
	}

	@Autowired
	public void setDataSource(DataSource dataSource) {
		simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
		createContract = new SimpleJdbcInsert(dataSource).withTableName(
				"contract").usingGeneratedKeyColumns("id");
	}

}
